////////////////////////////////////////////////////////////////////////////////
// Copyright 2005 CORE Solutions Limited
// All rights reserved.
// PROPRIETARY AND CONFIDENTIAL INFORMATION.
// Use is subject to license terms.
//
// FUNCTION ID      :   SO
// AUTHOR           :   Victor.zheng 
// DATE CREATE      :   2005/10/25
// DESCRIPTION      :   a DAO file for the function, SO
//                  :   
//                  :   
//                  :   
//
// MODIFICATION HISTORY:
// DATE         AUTHOR          VERSION NO.         COMMENT
// -----------  --------------  ------------------  -------------------
// 2005/10/25   Victor.zheng    1.0                 source is generated.
//
//
//
////////////////////////////////////////////////////////////////////////////////

package com.erp.app.login.taskEnquiry.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.erp.app.common.ProjectConstants;
import com.erp.app.login.taskEnquiry.criteria.TaskEnquirySrchCriteria;
import com.erp.app.login.taskEnquiry.pk.TaskEnquiryPk;
import com.erp.app.login.taskEnquiry.vo.TaskEnquiryVo;
import com.erp.framework.model.criteria.SimpleBaseSrchCriteria;
import com.erp.framework.model.dao.SimplePageEditStyleDao;
import com.erp.framework.model.vo.SimpleBaseVo;
import com.erp.framework.types.CustomBigDecimal;
import com.erp.framework.types.CustomSqlDate;
import com.erp.framework.util.CommonUtil;
import com.erp.framework.util.user.User;

/**
* <p>A <strong>DAO</strong>(Data Access Object) abstracts and encapsulates
* all access to the persistent store.  The DAO manages the connection with the
* data source to obtain and store data.  Also, the DAO implements
* the access mechanism required to work with the data source,
* and always provides a uniform API to its clients.
*
* @author          Victor.zheng 
* @version 1.0     2005/10/25
* @since           FrameWork 1.0
*
*/
public class TaskEnquiryDao extends SimplePageEditStyleDao {
    
    public void setMaxRows(Statement stmt) throws Exception{
        stmt.setMaxRows(ProjectConstants.DB_ENQUIRY_ROW_NUM);
    }

	/**
	* Returns the SQL select statement.
	* @param         obj     the searching criteria
	* @return        a string containing sql query
	*
	* @author     Victor.zheng 
	* @since      FrameWork 1.0
	*/
	protected String sqlQuery(SimpleBaseSrchCriteria obj) {

		TaskEnquirySrchCriteria criteria = (TaskEnquirySrchCriteria) obj;
		String whereClause = toWhereClause(criteria);
		String groupByClause = "";
		String orderByClause = "ORDER BY a.PROJ_NO";

		String sql = "SELECT a.COMPANY_CODE, a.PROJ_NO, a.APPROVE_DATE, a.LINE_NO, a.TASK_LINE_NO, a.TASK_DESC, a.DATE_COMPARE_STATUS, a.TASK_TYPE "
			+ "FROM V_TASK_ENQUIRY a  "
			+ whereClause
			+ groupByClause
			+ orderByClause;

		//super.logSqlQuery(sql);
		return sql;
	}

	/**
	* Returns the where clause of a SQL select statement.
	* @param         obj     the searching criteria
	* @return        a string containing the where clause
	*
	* @author     Victor.zheng 
	* @since      FrameWork 1.0
	*/
	private String toWhereClause(TaskEnquirySrchCriteria crit) {

		TaskEnquirySrchCriteria criteria = (TaskEnquirySrchCriteria) crit;
		StringBuffer whereClause = new StringBuffer();

		if (!CommonUtil.isEmpty(criteria.getSrchCompanyCode())) {
			whereClause.append(whereClause.length()== 0? "WHERE ": "AND ");
			whereClause.append("a.COMPANY_CODE = '" + criteria.getSrchCompanyCode().trim().toUpperCase()+"' ");
		}
		


		whereClause.append(whereClause.length()== 0? "WHERE ": "AND ");
		whereClause.append("a.APPROVE_DATE < sysdate  ");

		
		if (!CommonUtil.isEmpty(criteria.getSrchProjNoFr())) {
			whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
			whereClause.append("a.PROJ_NO >= '"
				+ criteria.getSrchProjNoFr().toUpperCase().trim() + "' ");
		}
		if (!CommonUtil.isEmpty(criteria.getSrchProjNoTo())) {
			whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
			whereClause.append("a.PROJ_NO <= '"
				+ criteria.getSrchProjNoTo().toUpperCase().trim() + "' ");
		}
        if (!CommonUtil.isEmpty(criteria.getSrchTaskType())) {
            whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
            whereClause.append(" a.TASK_TYPE = '"
                    + criteria.getSrchTaskType() + "' "
                   );
        }
        
        //*******************************security**********************************************
        
        
        whereClause.append(" AND EXISTS(SELECT 1 "
				+ " 			FROM T_USER_BRAND F1 "
				+ "				WHERE F1.COMPANY_CODE = '" + criteria.getSrchCompanyCode() + "' "
				+ "				AND F1.USER_CODE = '" + criteria.getSrchUserId() + "' "
				+ "				AND F1.BRAND_CODE = c.BRAND_CODE "
				+ "				AND F1.STATUS = '" + ProjectConstants.GENERIC_ACTIVE + "') ");
        whereClause.append(" AND EXISTS(SELECT 1 "
				+ " 			FROM T_USER_BUYER F2 "
				+ "				WHERE F2.COMPANY_CODE = '" + criteria.getSrchCompanyCode() + "' "
				+ "				AND F2.USER_CODE = '" + criteria.getSrchUserId() + "' "
				+ "				AND F2.BUYER_CODE = b.CUST_CODE "
				+ "				AND F2.STATUS = '" + ProjectConstants.GENERIC_ACTIVE + "') ");
        
		if(ProjectConstants.CD_TASK_ENQUIRY_TYPE_FABRIC.equals(CommonUtil.nullToString(criteria.getSrchTaskType()))){
			whereClause.append(" AND ( b.ACCESS_TEAM IS NULL OR  EXISTS(SELECT 1 "
					+ " 			FROM T_USER_ACCESS_TEAM F3 "
					+ "				WHERE F3.COMPANY_CODE = '" + criteria.getSrchCompanyCode() + "' "
					+ "				AND F3.USER_CODE = '" + criteria.getSrchUserId() + "' "
					+ "				AND F3.ACCESS_TEAM_CODE = b.ACCESS_TEAM "
					+ "				AND F3.STATUS = '" + ProjectConstants.GENERIC_ACTIVE + "') )");
	       whereClause.append(" AND (b.NT_TEAM IS NULL OR  EXISTS(SELECT 1 "
					+ " 			FROM T_USER_NT_TEAM F4 "
					+ "				WHERE F4.COMPANY_CODE = '" + criteria.getSrchCompanyCode() + "' "
					+ "				AND F4.USER_CODE = '" + criteria.getSrchUserId() + "' "
					+ "				AND F4.NT_TEAM_CODE = b.NT_TEAM "
					+ "				AND F4.STATUS = '" + ProjectConstants.GENERIC_ACTIVE + "') )");
		}else{
			whereClause.append(" AND (b.MERCH_ACCESS_TEAM IS NULL OR  EXISTS(SELECT 1 "
					+ " 			FROM T_USER_ACCESS_TEAM F3 "
					+ "				WHERE F3.COMPANY_CODE = '" + criteria.getSrchCompanyCode() + "' "
					+ "				AND F3.USER_CODE = '" + criteria.getSrchUserId() + "' "
					+ "				AND  F3.ACCESS_TEAM_CODE = b.MERCH_ACCESS_TEAM "
					+ "				AND F3.STATUS = '" + ProjectConstants.GENERIC_ACTIVE + "') )");
	        whereClause.append(" AND (b.MERCH_NT_TEAM IS NULL OR EXISTS(SELECT 1 "
					+ " 			FROM T_USER_NT_TEAM F4 "
					+ "				WHERE F4.COMPANY_CODE = '" + criteria.getSrchCompanyCode() + "' "
					+ "				AND F4.USER_CODE = '" + criteria.getSrchUserId() + "' "
					+ "				AND  F4.NT_TEAM_CODE = b.MERCH_NT_TEAM "
					+ "				AND F4.STATUS = '" + ProjectConstants.GENERIC_ACTIVE + "')) ");
		}
       
		//*******************************security end**********************************************
		
        
		return whereClause.toString();
	}

	/**
	* Constructs a string of SQL insert statement
	* @return        a string of SQL insert statement
	*
	* @author     Victor.zheng 
	* @since      FrameWork 1.0
	*/
	protected String sqlInsert() {
		String sql = "";

//		String sql = "INSERT INTO T_SO_HDR ("
//			+ "COMPANY_CODE, SO_NO, SO_DATE, CUST_CODE, TEL_NO_COUNTRY, "
//			+ "TEL_NO_CITY, TEL_NO, PROJECT_CODE, FAX_NO, CUST_PO_NO, "
//			+ "CUST_DOC_DATE, CONTACT, EMAIL, COUNTRY_ORIGIN, ADDRESS_1, "
//			+ "ADDRESS_2, ADDRESS_3, ADDRESS_4, PORT_LOADING, DESTINATION, "
//			+ "TRADE_TERMS, PAYMENT_TERMS, SHIP_VIA, HANDLED_BY, CURRENCY, "
//			+ "AMOUNT, REMARK, REVISION_NO, CREATE_USER, CREATE_DATE, "
//			+ "UPDATE_USER, UPDATE_DATE, STATUS, EXCHANGE_RATE, DEPT_CODE, "
//			+ "EX_FACTORY_DATE, SHIPMENT_DATE, DIVISION, FORWARDER, SHIP_ROUTE, "
//			+ "DIVISION_DESC, PREPARED_BY, GEN_FROM_PI, FORWARDER_REF, IS_CUST_INV, "
//			+ "AIR_FREIGHT) "
//			+ "VALUES ("
//			+ "?, ?, ?, ?, ?, "
//			+ "?, ?, ?, ?, ?, "
//			+ "?, ?, ?, ?, ?, "
//			+ "?, ?, ?, ?, ?, "
//			+ "?, ?, ?, ?, ?, "
//			+ "?, ?, ?, ?, SYSDATE, "
//			+ "?, ?, ?, ?, ?, "
//			+ "?, ?, ?, ?, ?, "
//			+ "?, ?, ?, ?, ?, "
//			+ "?)";
//
		//super.logSqlQuery(sql);
		return sql;
	}

	/**
	* Sets the values for a SQL insert or update statement.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming VO
	*
	* @author     Victor.zheng 
	* @since      FrameWork 1.0
	*/
	protected void setValuesForInsertAndModify(PreparedStatement pStmt, Object obj, String opType)
		throws SQLException {

//		SoEnquiryVo vo = (SoEnquiryVo) obj;
//		int i = 1;
//
//		pStmt.setString(i++, vo.getCompanyCode());
//		pStmt.setString(i++, vo.getSoNo());
//		setNullableDate(pStmt, i++, vo.getSoDate());
//		setNullableString(pStmt, i++, vo.getCustCode());
//		setNullableString(pStmt, i++, vo.getTelNoCountry());
//		setNullableString(pStmt, i++, vo.getTelNoCity());
//		setNullableString(pStmt, i++, vo.getTelNo());
//		setNullableString(pStmt, i++, vo.getProjectCode());
//		setNullableString(pStmt, i++, vo.getFaxNo());
//		setNullableString(pStmt, i++, vo.getCustPoNo());
//		setNullableDate(pStmt, i++, vo.getCustDocDate());
//		setNullableString(pStmt, i++, vo.getContact());
//		setNullableString(pStmt, i++, vo.getEmail());
//		setNullableString(pStmt, i++, vo.getCountryOrigin());
//		setNullableString(pStmt, i++, vo.getAddress1());
//		setNullableString(pStmt, i++, vo.getAddress2());
//		setNullableString(pStmt, i++, vo.getAddress3());
//		setNullableString(pStmt, i++, vo.getAddress4());
//		setNullableString(pStmt, i++, vo.getPortLoading());
//		setNullableString(pStmt, i++, vo.getDestination());
//		setNullableString(pStmt, i++, vo.getTradeTerms());
//		setNullableString(pStmt, i++, vo.getPaymentTerms());
//		setNullableString(pStmt, i++, vo.getShipVia());
//		setNullableString(pStmt, i++, vo.getHandledBy());
//		setNullableString(pStmt, i++, vo.getCurrency());
//		setNullableBigDecimal(pStmt, i++, vo.getAmount());
//		setNullableString(pStmt, i++, vo.getRemark());
//		setNullableBigInteger(pStmt, i++, vo.getRevisionNo());
//		setNullableString(pStmt, i++, vo.getCreateUser());
//
//		if (ProjectConstants.DB_OP_UPDATE.equals(opType))
//			setNullableTimestamp(pStmt, i++, vo.getCreateDate());
//
//		setNullableString(pStmt, i++, vo.getUpdateUser());
//
//		if (ProjectConstants.DB_OP_INSERT.equals(opType))
//			setNullableTimestamp(pStmt, i++, vo.getUpdateDate());
//		setNullableString(pStmt, i++, vo.getStatus());
//		setNullableBigDecimal(pStmt, i++, vo.getExchangeRate());
//		setNullableString(pStmt, i++, vo.getDeptCode());
//		setNullableDate(pStmt, i++, vo.getExFactoryDate());
//		setNullableDate(pStmt, i++, vo.getShipmentDate());
//		setNullableString(pStmt, i++, vo.getDivision());
//		setNullableString(pStmt, i++, vo.getForwarder());
//		setNullableString(pStmt, i++, vo.getShipRoute());
//		setNullableString(pStmt, i++, vo.getDivisionDesc());
//		setNullableString(pStmt, i++, vo.getPreparedBy());
//		setNullableString(pStmt, i++, vo.getGenFromPi());
//		setNullableString(pStmt, i++, vo.getForwarderRef());
//		setNullableString(pStmt, i++, vo.getIsCustInv());
//		setNullableString(pStmt, i++, vo.getAirFreight());
//
//		if(ProjectConstants.DB_OP_UPDATE.equals(opType)){
//			pStmt.setString(i++, vo.getCompanyCode());
//			pStmt.setString(i++, vo.getSoNo());
//			setNullableTimestamp(pStmt, i++, vo.getUpdateDate());
//		}
	}

	/**
	* Sets the values for a SQL insert statement.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming VO
	*
	* @author     Victor.zheng 
	* @since      FrameWork 1.0
	*/
	protected void setValuesForInsert(PreparedStatement pStmt, Object obj)
		throws SQLException {

		//setValuesForInsertAndModify(pStmt, obj, ProjectConstants.DB_OP_INSERT);
	}

	/**
	* Constructs a string of SQL update statement
	* @return        a string of SQL update statement
	*
	* @author     Victor.zheng 
	* @since      FrameWork 1.0
	*/
	protected String sqlUpdate() {
		String sql = "";

//		String sql = "UPDATE T_SO_HDR SET COMPANY_CODE=?, SO_NO=?, SO_DATE=?, CUST_CODE=?, TEL_NO_COUNTRY=?, "
//			+ "TEL_NO_CITY=?, TEL_NO=?, PROJECT_CODE=?, FAX_NO=?, CUST_PO_NO=?, "
//			+ "CUST_DOC_DATE=?, CONTACT=?, EMAIL=?, COUNTRY_ORIGIN=?, ADDRESS_1=?, "
//			+ "ADDRESS_2=?, ADDRESS_3=?, ADDRESS_4=?, PORT_LOADING=?, DESTINATION=?, "
//			+ "TRADE_TERMS=?, PAYMENT_TERMS=?, SHIP_VIA=?, HANDLED_BY=?, CURRENCY=?, "
//			+ "AMOUNT=?, REMARK=?, REVISION_NO=?, CREATE_USER=?, CREATE_DATE=?, "
//			+ "UPDATE_USER=?, UPDATE_DATE=SYSDATE, STATUS=?, EXCHANGE_RATE=?, DEPT_CODE=?, "
//			+ "EX_FACTORY_DATE=?, SHIPMENT_DATE=?, DIVISION=?, FORWARDER=?, SHIP_ROUTE=?, "
//			+ "DIVISION_DESC=?, PREPARED_BY=?, GEN_FROM_PI=?, FORWARDER_REF=?, IS_CUST_INV=?, "
//			+ "AIR_FREIGHT=? "
//			+ "WHERE COMPANY_CODE=? AND SO_NO=? "
//			+ "AND (UPDATE_DATE IS NULL OR UPDATE_DATE = ?) ";

		//super.logSqlQuery(sql);
		return sql;
	}

	/**
	* Sets the values for a SQL update statement.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming VO
	*
	* @author     Victor.zheng 
	* @since      FrameWork 1.0
	*/
	protected void setValuesForModify(PreparedStatement pStmt, Object obj)
		throws SQLException {

		//setValuesForInsertAndModify(pStmt, obj, ProjectConstants.DB_OP_UPDATE);
	}

    /**
    * Constructs a string of SQL delete statement
    * @return        a string of SQL update statement
    *
    * @author     Victor.zheng 
    * @since      FrameWork 1.0
    */
    protected String sqlDelete() {
        //String sql = "UPDATE T_SO_HDR SET ACTIVE_IND='" + GENERIC_NO + "', INACTIVE_DATE=SYSDATE, ACTIVE_DATE=NULL WHERE COMPANY_CODE=? AND SO_NO=? ";
    	String sql = "";

        //super.logSqlQuery(sql);
        return sql;
    }

    /**
    * Sets the values for a SQL delete statement.
    * @param         pStmt   the incoming prepared statement
    * @param         obj     the incoming VO
    *
    * @author     Victor.zheng 
    * @since      FrameWork 1.0
    */
    protected void setValuesForDelete(PreparedStatement pStmt, Object obj)
        throws SQLException {

//        SalesContractHdrVO vo = (SalesContractHdrVO) obj;
//        int i = 1;
//
//        pStmt.setString(i++, vo.getCompanyCode());
//        pStmt.setString(i++, vo.getSoNo());
    }    

	/**
	* Constructs a string of SQL select statement
	* @return        a string of SQL select statement
	*
	* @author     Victor.zheng 
	* @since      FrameWork 1.0
	*/
	protected String sqlFind() {
/*
		String sql = "SELECT COMPANY_CODE, SO_NO, SO_DATE, CUST_CODE "
			+ "FROM T_SO_HDR "
			+ "WHERE COMPANY_CODE=? AND SO_NO=? "
			+ "ORDER BY COMPANY_CODE, SO_NO";*/

		//super.logSqlQuery(sql);
		return "";
	}

	/**
	* Sets the values for a SQL select statement for finding by primary key.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming PK
	*
	* @author     Victor.zheng 
	* @since      FrameWork 1.0
	*/
	protected void setValuesForFind(PreparedStatement pStmt, Object obj)
		throws SQLException {

		TaskEnquiryPk pk = (TaskEnquiryPk) obj;
		int i = 1;

//		pStmt.setString(i++, pk.getCompanyCode());
//		pStmt.setString(i++, pk.getSoNo());
	}

	/**
	* Constructs a string of SQL select statement
	* @return        a string of SQL select statement
	*
	* @author     Victor.zheng 
	* @since      FrameWork 1.0
	*/
	protected String sqlList() {

		return null;	}

	/**
	* Sets the values for a SQL select statement for finding child tables.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming parent PK
	*
	* @author     Victor.zheng 
	* @since      FrameWork 1.0
	*/
	protected void setValuesForList(PreparedStatement pStmt, Object obj)
		throws SQLException {

	}

	/**
	* Sets the values of a result set to a vo
	* @param         rs   the incoming result set
	* @return        a vo containing the result set values
	*
	* @author     Victor.zheng 
	* @since      FrameWork 1.0
	*/
	protected SimpleBaseVo getResultSetValues(ResultSet rs) throws SQLException {

		TaskEnquiryVo vo = new TaskEnquiryVo();

		vo.setCompanyCode(rs.getString("COMPANY_CODE"));
		vo.setProjNo(rs.getString("PROJ_NO"));
		vo.setLineNo(CommonUtil.bigDecimalToStr(CustomBigDecimal.customize(rs.getBigDecimal("LINE_NO"))));
		vo.setTaskLineNo(CommonUtil.bigDecimalToStr(CustomBigDecimal.customize(rs.getBigDecimal("TASK_LINE_NO"))));
		vo.setTaskDesc(rs.getString("TASK_DESC"));
		
		vo.setApproveDate(CustomSqlDate.customize(rs.getDate("APPROVE_DATE")));
		vo.setTaskType(rs.getString("TASK_TYPE"));
		vo.setDateCompareStatus(rs.getString("DATE_COMPARE_STATUS"));
//		vo.setCustCode(rs.getString("CUST_CODE"));
//		vo.setTelNoCountry(rs.getString("TEL_NO_COUNTRY"));
//		vo.setTelNoCity(rs.getString("TEL_NO_CITY"));
//		vo.setTelNo(rs.getString("TEL_NO"));
//		vo.setProjectCode(rs.getString("PROJECT_CODE"));
//		vo.setFaxNo(rs.getString("FAX_NO"));
//		vo.setCustPoNo(rs.getString("CUST_PO_NO"));
//		vo.setCustDocDate(CustomSqlDate.customize(rs.getDate("CUST_DOC_DATE")));
//		vo.setContact(rs.getString("CONTACT"));
//		vo.setEmail(rs.getString("EMAIL"));
//		vo.setCountryOrigin(rs.getString("COUNTRY_ORIGIN"));
//		vo.setAddress1(rs.getString("ADDRESS_1"));
//		vo.setAddress2(rs.getString("ADDRESS_2"));
//		vo.setAddress3(rs.getString("ADDRESS_3"));
//		vo.setAddress4(rs.getString("ADDRESS_4"));
//		vo.setPortLoading(rs.getString("PORT_LOADING"));
//		vo.setDestination(rs.getString("DESTINATION"));
//		vo.setTradeTerms(rs.getString("TRADE_TERMS"));
//		vo.setPaymentTerms(rs.getString("PAYMENT_TERMS"));
//		vo.setShipVia(rs.getString("SHIP_VIA"));
//		vo.setHandledBy(rs.getString("HANDLED_BY"));
//		vo.setCurrency(rs.getString("CURRENCY"));
//		vo.setAmount(CustomBigDecimal.customize(rs.getBigDecimal("AMOUNT")));
//		vo.setRemark(rs.getString("REMARK"));
//		vo.setRevisionNo(CustomBigInteger.customize(rs.getBigDecimal("REVISION_NO")));
//		vo.setCreateUser(rs.getString("CREATE_USER"));
//		vo.setCreateDate(rs.getTimestamp("CREATE_DATE"));
//		vo.setUpdateUser(rs.getString("UPDATE_USER"));
//		vo.setUpdateDate(rs.getTimestamp("UPDATE_DATE"));
//		vo.setStatus(rs.getString("STATUS"));
//		vo.setExchangeRate(CustomBigDecimal.customize(rs.getBigDecimal("EXCHANGE_RATE")));
//		vo.setDeptCode(rs.getString("DEPT_CODE"));
//		vo.setExFactoryDate(CustomSqlDate.customize(rs.getDate("EX_FACTORY_DATE")));
//		vo.setShipmentDate(CustomSqlDate.customize(rs.getDate("SHIPMENT_DATE")));
//		vo.setDivision(rs.getString("DIVISION"));
//		vo.setForwarder(rs.getString("FORWARDER"));
//		vo.setShipRoute(rs.getString("SHIP_ROUTE"));
//		vo.setDivisionDesc(rs.getString("DIVISION_DESC"));
//		vo.setPreparedBy(rs.getString("PREPARED_BY"));
//		vo.setGenFromPi(rs.getString("GEN_FROM_PI"));
//		vo.setForwarderRef(rs.getString("FORWARDER_REF"));
//		vo.setIsCustInv(rs.getString("IS_CUST_INV"));
//		vo.setAirFreight(rs.getString("AIR_FREIGHT"));

		return vo;
	}

}
